create external table jms_dm.dm_jqr_cross_belt_sort_cnt_dt (
   date_time date comment '查询时间',
   network_code string comment '网点code',
   network_name string comment '网点名称(中心)' ,
   sorting_plan_code string comment '分拣方案编码',
   sorting_plan_name string comment '方案名称',
   equipment_code string comment '设备编号',
   oprate_type string comment '操作模式',
   sort_cnt int comment '分拣量',
   usage_duration decimal(16,1) comment '使用时长'
) COMMENT '沈阳转运中心交叉带机器人报表'
PARTITIONED BY (dt STRING COMMENT '日期分区yyyy-MM-dd')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_jqr_cross_belt_sort_cnt_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);
------------
create table dm_jqr_cross_belt_sort_cnt_dt (
    date_time date,
    network_code NVARCHAR2(50),
    network_name NVARCHAR2(100),
    sorting_plan_code NVARCHAR2(100),
    sorting_plan_name NVARCHAR2(100),
    equipment_code NVARCHAR2(100),
    oprate_type NVARCHAR2(100),
    sort_cnt int,
    usage_duration decimal(16,1),
    etl_update_time date )
partition by range (date_time)
interval (numtodsinterval(1,'day'))
store in(jms_01_data,jms_02_data,jms_03_data,jms_04_data,jms_05_data)
(partition p1 values less than (to_date('2020-01-01','yyyy-mm-dd')))
;

create index dm_jqr_cross_belt_sort_cnt_dt_idx1 on dm_jqr_cross_belt_sort_cnt_dt (date_time) nologging  local;

comment on table  dm_jqr_cross_belt_sort_cnt_dt is ' 沈阳转运中心交叉带机器人报表';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.date_time is '查询时间';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.network_code is '网点code';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.network_name is '网点名称(中心)';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.sorting_plan_code is '分拣方案编码';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.sorting_plan_name is '方案名称';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.equipment_code is '设备编号';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.oprate_type is '操作模式';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.sort_cnt is '分拣量';
comment on  column  dm_jqr_cross_belt_sort_cnt_dt.usage_duration is '使用时长'
